package com.yenlien.traceyun.plant.dao;


import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.ShiJiRuiTong;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author jialong
 * @date 2021/03/31
 * @description:
 **/

public interface ShiJiRuiTongDao extends BaseJpaDao<ShiJiRuiTong> {
    // ELECT * FROM t_sjrt WHERE REPLACE(CONVERT(equipment_name USING ASCII), "?", "") = '866250060598764' ORDER BY c_time

    // 速度太慢
    @Query(value = "SELECT ANY_VALUE(id) id, ANY_VALUE(c_time) ctime, param_name paramName, ANY_VALUE(param_unit) paramUnit, ANY_VALUE(param_val) paramVal, " +
            "ANY_VALUE(company_id) companyId, ANY_VALUE(equipment_id) equipmentId, ANY_VALUE(type) type, ANY_VALUE(`at`) `at`, ANY_VALUE(base_soil_id) baseSoilId, " +
            "ANY_VALUE(equipment_name) equipmentName, ANY_VALUE(cell_voltage) cellVoltage, ANY_VALUE(charge_voltage) chargeVoltage, ANY_VALUE(num) num, " +
            "ANY_VALUE(`key`) `key` , ANY_VALUE(c_time) val FROM " +
            "(SELECT * FROM t_sjrt WHERE equipment_id = ?1 HAVING 1 ORDER BY c_time DESC) t GROUP BY t.param_name", nativeQuery = true)
    List<JSONObject> getLast(String equId);


//    @Query(value = "SELECT id, c_tim ctime, param_name paramName, param_unit paramUnit, param_val paramVal, company_id companyId, equipment_id equipmentId, type, `at`," +
//            "base_soil_id baseSoilId, equipment_name equipmentName, cell_voltage cellVoltage, charge_voltage chargeVoltage, num, `key`, c_time val " +
//            "FROM t_sjrt WHERE equipment_id = ?1 ORDER BY c_time DESC LIMIT ?2", nativeQuery = true)
    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 ORDER BY create_time DESC LIMIT ?2", nativeQuery = true)
    List<ShiJiRuiTong> getLast(String equId, Integer limit);




    @Query(value = "SELECT param_name paramName, ANY_VALUE(param_unit) paramUnit FROM t_sjrt WHERE equipment_id = ?1 GROUP BY param_name", nativeQuery = true)
    List<JSONObject> getParamNameList(String equId);


    @Query(value = "SELECT GROUP_CONCAT(CONCAT(param_name, '=', param_val)) FROM t_sjrt WHERE create_time IS NOT NULL AND equipment_id = ?1 GROUP BY create_time", nativeQuery = true)
    List<String> getByCreateTime(String equId);


    @Query(value = "SELECT param_val paramVal, c_time `time` FROM t_sjrt WHERE equipment_id = ?1 AND param_name = ?2", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName);

    @Query(value = "SELECT param_val paramVal, c_time `time` FROM t_sjrt WHERE equipment_id = ?1 AND param_name = ?2 AND c_time BETWEEN ?3 AND ?4", nativeQuery = true)
    List<JSONObject> getByParamName(String equId, String paramName, String startTime, String endTime);

    @Query(value = "SELECT GROUP_CONCAT(CONCAT(param_name, '=', param_val)) FROM t_sjrt WHERE create_time IS NOT NULL AND equipment_id = ?1 AND create_time BETWEEN ?2 AND ?3 GROUP BY create_time", nativeQuery = true)
    List<String> getByCreateTime(String equId, String startTime, String endTime);




    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 ORDER BY c_time DESC LIMIT 20", nativeQuery = true)
    List<ShiJiRuiTong> getInsectHistoryData(String devId);


    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 ORDER BY c_time DESC", nativeQuery = true)
    List<ShiJiRuiTong> getHistoryData(String devId, String startTime, String endTime);


    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 ORDER BY c_time DESC LIMIT ?2", nativeQuery = true)
    List<ShiJiRuiTong> getKillHistoryData(String devId, Integer limit);




    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 ORDER BY c_time DESC", nativeQuery = true)
    Page<ShiJiRuiTong> getHistoryData(String devId, String startTime, String endTime, Pageable pageable);

    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 AND DATE_FORMAT(c_time, '%Y-%m-%d') = ?2 ORDER BY c_time DESC limit 20", nativeQuery = true)
    List<ShiJiRuiTong> getWaterData(String devId, String time);

    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1  AND param_name= ?2 AND c_time BETWEEN ?3 AND ?4", nativeQuery = true)
    List<ShiJiRuiTong> getHistoryData(String devId, String paramName, String startTime, String endTime);


//    @Query(value = "SELECT * FROM t_sjrt WHERE REPLACE(CONVERT(equipment_name USING ASCII), '?', '') = ?1 AND c_time BETWEEN ?2 AND ?3", nativeQuery = true)
//    List<ShiJiRuiTong> findHistoryData(String devId, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 AND c_time BETWEEN ?2 AND ?3 ORDER BY c_time DESC", nativeQuery = true)
    List<ShiJiRuiTong> findHistoryData(String devId, String startTime, String endTime);

    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 ORDER BY c_time DESC", nativeQuery = true)
    List<ShiJiRuiTong> getHistoryData(String devId);

    @Query(value = "SELECT * FROM t_sjrt WHERE equipment_id = ?1 ORDER BY c_time DESC", nativeQuery = true)
    Page<ShiJiRuiTong> getHistoryData(String devId, Pageable pageable);

    @Query(value = "SELECT id FROM t_sjrt WHERE type = ?1 AND DATE_FORMAT(c_time, '%Y-%m-%d') = ?2 LIMIT 1", nativeQuery = true)
    String checkInsectData(String type, String time);

    @Query(value = "SELECT * FROM t_sjrt WHERE type = '3' AND company_id IN (SELECT tc.id FROM t_company tc WHERE tc.parent_id = ?1) AND c_time > DATE_SUB(CURDATE(), INTERVAL 6 DAY) ORDER BY c_time DESC", nativeQuery = true)
    List<ShiJiRuiTong> getDGInsect(String companyId);

    @Query(value = "SELECT * FROM t_sjrt WHERE type = '3' AND company_id = ?1 AND c_time > DATE_SUB(CURDATE(), INTERVAL 6 DAY) ORDER BY c_time DESC", nativeQuery = true)
    List<ShiJiRuiTong> getDGInsect2(String companyId);

//    @Query(value = "SELECT * FROM t_sjrt WHERE type = '3' AND company_id  = ?1 AND c_time > DATE_SUB(CURDATE(), INTERVAL 6 DAY) ORDER BY c_time DESC", nativeQuery = true)
    @Query(value = "SELECT * FROM t_sjrt WHERE type = '3' AND company_id  = ?1  ORDER BY c_time DESC LIMIT 8", nativeQuery = true)
    List<ShiJiRuiTong> getCWInsect(String companyId);



    @Query(value = "SELECT * FROM t_sjrt WHERE type = '4' AND company_id IN (SELECT tc.id FROM t_company tc WHERE tc.parent_id = ?1) ORDER BY c_time DESC limit 10", nativeQuery = true)
    List<ShiJiRuiTong> getWater(String companyId);


    @Query(value = "SELECT * FROM t_sjrt WHERE type = '4' AND company_id = ?1 ORDER BY c_time DESC limit 10", nativeQuery = true)
    List<ShiJiRuiTong> getWater2(String companyId);

    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_sjrt WHERE date_format(c_time, '%Y-%m') = ?1 AND type = ?2 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String devType);

    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_sjrt WHERE date_format(c_time, '%Y-%m') = ?1 AND type = ?2 AND company_id = ?3 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysis(String yearMonth, String devType, String fType);

    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_sjrt " +
            "WHERE date_format(c_time, '%Y-%m') = ?1 AND equipment_id = ?2 AND company_id = ?3 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysisEqu(String yearMonth, String equId, String fType);


    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_sjrt " +
            "WHERE date_format(c_time, '%Y-%m') = ?1 AND equipment_id = ?2 AND param_name = ?3 GROUP BY param_name ", nativeQuery = true)
    JSONObject getAnalysisName(String yearMonth, String equId, String paramName);


    @Query(value = "SELECT param_name paramName, MAX(param_val) max, MIN(param_val) min, ROUND(AVG(param_val), 2) avg FROM t_sjrt " +
            "WHERE date_format(c_time, '%Y-%m-%d') = ?1 AND equipment_id = ?2 AND company_id = ?3 GROUP BY param_name ", nativeQuery = true)
    List<JSONObject> getAnalysisEquDay(String yearMonth, String equId, String fType);


    @Query(value = "SELECT param_val paramVal, c_time cTime FROM t_sjrt WHERE date_format(c_time, '%Y-%m') = ?1 AND param_name = ?2", nativeQuery = true)
    List<JSONObject> getLine(String yearMonth, String paramName);

    @Query(value = "SELECT param_val paramVal, c_time cTime FROM t_sjrt WHERE date_format(c_time, '%Y-%m') = ?1 AND param_name = ?2 AND company_id = ?3", nativeQuery = true)
    List<JSONObject> getLine(String yearMonth, String paramName, String fType);


    @Query(value = "SELECT param_val paramVal, param_name paramName, c_time cTime, equipment_id devId FROM t_sjrt WHERE date_format(c_time, '%Y-%m') = ?1 AND param_name = ?2 AND equipment_id = ?3", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String paramName, String equId);

    @Query(value = "SELECT param_val paramVal, param_name paramName, c_time cTime, equipment_id devId, param_unit unit FROM t_sjrt WHERE date_format(c_time, '%Y-%m') = ?1  AND equipment_id = ?2 ORDER BY c_time", nativeQuery = true)
    List<JSONObject> getGraphAnalyse(String yearMonth, String equId);




}
